﻿' ------------------------------------------------------------------------------
' The SqlInspectorForm handles SQL events raised by the application and
' SpatialAlbum.Core. It contains a listbox with short names. When an
' event is selected from the list, the SQL command details are shown in
' rest of the form. Command parameters are also display. The SQL
' statement is reformatted into a more-or-less standard format before
' being display.
'
' The SqlEvent() method creates a SqlItem from the event args and adds
' it to the list box in the form. SqlItem copies select parts of the
' SqlCommand object in the SqlEventArgs in case the client subsequently
' changes and reuses the SqlComman. If the last item in the listbox was
' selected, the new item becomes selected and its details are displayed
' in the other windows.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Class SqlInspectorForm

    Private Class SqlItem
        Dim m_event As SqlEventArgs
        Dim m_cmd As SqlCommand
        Dim m_id As Integer = 0
        Shared m_nextid As Integer = 1
        Shared m_nullitem As SqlItem = Nothing
        Public Shared Function NullItem() As SqlItem
            If m_nullitem Is Nothing Then m_nullitem = New SqlItem(New SqlEventArgs())
            Return m_nullitem
        End Function
        Private ReadOnly Property UniqueName() As String
            Get
                Return "SqlEvent" & m_id.ToString
            End Get
        End Property
        Public ReadOnly Property Name() As String
            Get
                If m_event.Name = "" Then Return UniqueName
                Return m_event.Name
            End Get
        End Property
        Public ReadOnly Property Message() As String
            Get
                If m_event.Name = "" Then Return UniqueName
                Return m_event.Message
            End Get
        End Property
        Public ReadOnly Property Command() As SqlCommand
            Get
                Return m_cmd
            End Get
        End Property
        Private Sub New() ' make inaccessible
        End Sub
        Public Sub New(ByVal e As SqlEventArgs)

            m_event = e
            m_id = m_nextid
            m_nextid += 1

            ' copy parts of the SqlCommand in case the client reuses and changes it
            If m_event.Command IsNot Nothing Then
                m_cmd = New SqlCommand(e.Command.CommandText)
                For Each p As SqlParameter In e.Command.Parameters
                    m_cmd.Parameters.Add(New SqlParameter(p.ParameterName, p.Value))
                Next
            End If

        End Sub
        Public Overrides Function ToString() As String
            Select Case m_event.Type
                Case EnumSqlEvent.eOpenSqlFilestreamError : Return Name
                Case EnumSqlEvent.eOperation : Return Name
                Case EnumSqlEvent.eCommand : Return Message
                Case Else : Return UniqueName & ":" & m_event.Type.ToString
            End Select
        End Function
    End Class

    Private Sub SqlInspectorForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lvwParameters.Columns.Item(0).Width = 150
        lvwParameters.Columns.Item(1).Width = 350
        HideParameters()
        AddHandler Me.DragDrop, AddressOf DragDropHelper.OnDragDrop
        AddHandler Me.DragEnter, AddressOf DragDropHelper.OnDragEnter
    End Sub

    Private Sub SqlInspectorForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub SqlInspectorForm_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
        If lvwParameters.Visible Then
            txtSql.Height = lbxCommands.Height - lvwParameters.Height
        Else
            txtSql.Height = lbxCommands.Height
        End If
    End Sub

    Private Delegate Sub DlgSqlEvent(ByVal e As SqlEventArgs)
    Public Sub SqlEvent(ByVal e As SqlEventArgs)
        If InvokeRequired Then
            BeginInvoke(New DlgSqlEvent(AddressOf SqlEvent), e)
        Else
            Dim si As SqlItem = New SqlItem(e)
            lbxCommands.Items.Add(si)

            ' select/display the item only if it's the last in the list
            If lbxCommands.Items.Count = 1 OrElse lbxCommands.SelectedIndex = lbxCommands.Items.Count - 2 Then ShowSqlItem(si)
        End If
    End Sub

    Private Sub ShowSqlItem(ByVal si As SqlItem)
        If si Is SqlItem.NullItem Then Return

        If si.Command Is Nothing Then
            txtMessage.Text = si.Name
            txtSql.Text = si.Message
            HideParameters()
        Else
            ' clean up/standardize the sql command
            Dim sql As String = si.Command.CommandText.Replace(vbCrLf, " ").Replace(vbTab, " ")
            While sql.Contains("  ")
                sql = sql.Replace("  ", " ")
            End While
            While sql.Contains(";" & vbLf)
                sql = sql.Replace(";" & vbLf, ";")
            End While
            While sql.Contains("; ")
                sql = sql.Replace("; ", ";")
            End While

            sql = sql.Replace(" FROM ", vbLf & "FROM ") _
                     .Replace(" VALUES ", vbLf & "VALUES ") _
                     .Replace(" WHERE ", vbLf & "WHERE ") _
                     .Replace(" ORDER ", vbLf & "ORDER ") _
                     .Replace(" SET ", vbLf & "SET ") _
                     .Replace(";", ";" & vbLf) _
                     .Replace(", ", "," & vbLf & vbTab) _
                     .Replace(";", ";" & vbLf)

            ' split sql into lines, show in the form
            Dim lines() As String = sql.Split(vbCrLf.ToCharArray)
            Dim sb As New System.Text.StringBuilder
            For Each line As String In lines
                sb.AppendLine(line)
            Next
            txtSql.Text = sb.ToString
            If si.Message <> "" Then txtMessage.Text = si.Message

            ' display the parameters
            If si.Command.Parameters.Count = 0 Then
                HideParameters()
            Else
                lvwParameters.BeginUpdate()
                lvwParameters.Items.Clear()
                For Each arg As SqlParameter In si.Command.Parameters
                    lvwParameters.Items.Add(New ListViewItem(New String() {arg.ParameterName, arg.Value.ToString}))
                    sb.Append(arg.ParameterName).Append(vbTab).AppendLine(arg.Value.ToString)
                Next
                lvwParameters.EndUpdate()
                ShowParameters()
            End If
        End If
        lbxCommands.SelectedItem = si
        Update()
    End Sub

    Private Sub HideParameters()
        SplitContainer1.Panel2Collapsed = True
    End Sub

    Private Sub ShowParameters()
        SplitContainer1.Panel2Collapsed = False
    End Sub

    Private Sub lbxCommands_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbxCommands.SelectedIndexChanged
        Dim i As Integer = lbxCommands.SelectedIndex
        If i < 0 Then Return
        Dim si As SqlItem = CType(lbxCommands.Items.Item(i), SqlItem)
        ShowSqlItem(si)
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        lbxCommands.Items.Clear()
        txtSql.Text = ""
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

End Class